package com.evan.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @ClassName: JdbcTest 
 * @Description: JDBC的增、删、改、查操作
 * @author Evan 
 * @date 2017年4月13日 下午12:38:04 
 *
 */
public class JdbcTest {

    public static void main(String[] args) {
        JdbcTest test = new JdbcTest();
        
        //查询
        //test.query();
        
        //插入
        //test.insert("wangwu", "1234");
        
        //修改
        //test.update(1,"888888");
        
        //删除
        test.delete(1);
    }
    
    /**
     * @Title: query 
     * @Description: 查询
     * @param     设定文件 
     * @return void    返回类型 
     * @throws
     */
    public void query(){
        //1、加载mysql驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        
        //2、创建连接
        Connection conn = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost/taotao", "root", "123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        //3、创建执行语句
        Statement stat = null;
        try {
            stat = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        String sql = "select id,username,password,phone,email from tb_user ";
        try {
            ResultSet result =  stat.executeQuery(sql);
            while(result.next()){
               System.out.println("1:"+result.getInt(1));
               System.out.println("2:"+result.getString(2));
               System.out.println("3:"+result.getString(3));
               System.out.println("4:"+result.getString(4));
               System.out.println("5:"+result.getString(5));
               System.out.println("====================");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            //关闭连接
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    public void insert(String username,String password){
        //加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        
        //创建连接
        Connection conn = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost/taotao","root","123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        //预执行语句
        String sql = "insert into user(username,password) values (?,?)";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            int result = pstmt.executeUpdate();
            if(result > 0){
                System.out.println("执行成功，影响的行数为："+result);
            }else{
                System.out.println("执行失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    /**
     * @Title: update 
     * @Description: 修改
     * @param @param id
     * @param @param password    设定文件 
     * @return void    返回类型 
     * @throws
     */
    public void update(int id,String password){
        //加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        
        Connection conn = null;
        //创建连接
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/taotao","root","123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        //预执行语句
        String sql = "update user set password = ? where id = ?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, password);
            pstmt.setInt(2, id);
            int result = pstmt.executeUpdate();
            if(result > 0){
                System.out.println("执行成功，影响的行数为："+result);
            }else{
                System.out.println("执行失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    public void delete(int id){
        //加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        
        //创建连接
        Connection conn = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/taotao", "root", "123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        //预执行语句
        String sql = "delete from user where id = ?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            
            int result = pstmt.executeUpdate();
            if(result > 0){
                System.out.println("执行成功，影响的行数为："+result);
            }else{
                System.out.println("执行失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
    }
}
